/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Bussiness;

import Connection.Department;
import Connection.DBUtil;
import Connection.Project;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author SONY
 */
public class DepartmentDAO {
    private static Connection conn = null;
    private static CallableStatement cs = null;
    private static ResultSet rs = null;
    private static Vector department;
    private static DBUtil objDBUtil = new DBUtil();
    private static Department objDepartment = new Department();
    public DepartmentDAO(){
    }

    public static void insertDepartmentToDB(String departmentID, String departmentName, int departmentAllowance ) throws SQLException {
        String SQL;
        DBUtil objDBUtil = new DBUtil();
        Connection conn = objDBUtil.connect();
            SQL = "INSERT INTO Department VALUES (?, ?, ?)";
            PreparedStatement pstmt = conn.prepareStatement(SQL);
            pstmt.setString(1, departmentID);
            pstmt.setString(2, departmentName);
            pstmt.setInt(3, departmentAllowance);
            pstmt.executeUpdate();
            conn.close();
    }
    
    public static void upDatetoDB(String departmentID, String departmentName, String departmentAllowance) throws SQLException{
            String SQL;
            DBUtil objDBUtil = new DBUtil();
            Connection conn = objDBUtil.connect();    
            SQL = "UPDATE Department SET DepartmentID=?, DepartmentName=?, DepartmentAllowance=?" + " WHERE DepartmentID=?";
            PreparedStatement pstmt = conn.prepareStatement(SQL);
            pstmt.setString(4, departmentID);
            pstmt.setString(1, departmentID);
            pstmt.setString(2, departmentName);
            pstmt.setString(3, departmentAllowance);
                //buoc 5: thuc thi cau lenh sql
            pstmt.executeUpdate();
            conn.close();
    }
    
    public static void deleteToDB(String DepartmentID) throws SQLException{
        String SQL;
        DBUtil objDBUtil = new DBUtil();
        Connection conn = objDBUtil.connect(); 
        SQL = "DELETE FROM Department WHERE DepartmentID=?";
        PreparedStatement pstmt = conn.prepareStatement(SQL);
        //String DepartmentID = tbBang.getValueAt(tbBang.getSelectedRow(), 1).toString();
        pstmt.setString(1, DepartmentID);
        pstmt.executeUpdate();
        conn.close();
    }

    public static String getDepartmentID(String DepartmentName) throws SQLException{
        conn = objDBUtil.connect();
        cs = conn.prepareCall("{call spGetDepartmentID(?)}");
        cs.setString(1, DepartmentName);
        rs = cs.executeQuery();
        String DepartmentID = null;
        while(rs.next()){
            DepartmentID = rs.getString(1);
        }
        return DepartmentID;
    }
    
    public static Vector getComboBoxDepartmentFromDB() throws SQLException {
        conn = objDBUtil.connect();
        cs = conn.prepareCall("{call spDepartment}");
        rs = cs.executeQuery();
        department = new Vector();
        department.add("Select");
        while (rs.next()) {
            department.addElement(rs.getString(3));
        }
        return department;
    }
    
    public static Vector getComboBoxDepartment() throws SQLException {
        conn = objDBUtil.connect();
        cs = conn.prepareCall("{call spDepartment}");
        rs = cs.executeQuery();
        department = new Vector();
        department.add("Select Department");
        while (rs.next()) {
            department.addElement(rs.getString(3));
        }
        return department;
    }
    
    public static Vector getComboBoxDepartmentIDFromDB() throws SQLException {
        conn = objDBUtil.connect();
        cs = conn.prepareCall("{call spDepartment}");
        rs = cs.executeQuery();
        department = new Vector();
        department.add("Select");
        while (rs.next()) {
            department.addElement(rs.getString(2));
        }
        return department;
    }
    public static String getDepartmentName(String DepartmentID) throws SQLException{
        conn = objDBUtil.connect();
        cs = conn.prepareCall("{call spGetDepartmentName(?)}");
        cs.setString(1, DepartmentID);
        rs = cs.executeQuery();
        String DepartmentName = null;
        while(rs.next()){
            DepartmentName = rs.getString(1);
        }
        return DepartmentName;
    }
    
    public static Department searchDepartmentByID(String departmentID) throws SQLException{
        conn = objDBUtil.connect();
        cs = conn.prepareCall("{call spSearchDepartmentByID(?)}");
        cs.setString(1, departmentID);
        rs =cs.executeQuery();
        while (rs.next()) {            
            objDepartment.setStt(rs.getInt(1));
            objDepartment.setDepartmentID(rs.getString(2));
            objDepartment.setDepartmentName(rs.getString(3));
            objDepartment.setDepartmentAllowance(rs.getInt(4));
        }
        return objDepartment;
    }
    
    public static int searchDepartmentByName(String departmentName) throws SQLException{
        conn = objDBUtil.connect();
        cs = conn.prepareCall("{call spSearchDepartmentByName(?)}");
        cs.setString(1, departmentName);
        rs =cs.executeQuery();
        int count = 0;
        while (rs.next()) {            
            count++;
        }
        return count;
    }
    
    public static int countSearchDepartmentID(String departmentID) throws SQLException{
        conn = objDBUtil.connect();
        cs = conn.prepareCall("{call spSearchDepartmentByID(?)}");
        cs.setString(1, departmentID);
        rs = cs.executeQuery();
        int count = 0;
        while (rs.next()){
            count++;
        }
        return count;
    }
    
    public static int countTotalDepartmetn() throws SQLException{
        conn = objDBUtil.connect();
        cs = conn.prepareCall("{call spDepartment}");
        rs = cs.executeQuery();
        int count = 0;
        while (rs.next()) {            
            count++;
        }
        return count;
    }
    
    public static int getDepartment(String departmentID) throws SQLException{
        conn = objDBUtil.connect();
        cs = conn.prepareCall("{call spGetAllowanceDepartment}");
        cs.setString(1, departmentID);
        rs = cs.executeQuery();
        int allowance = 0;
        while (rs.next()) {            
            allowance = rs.getInt(1);
        }
        return allowance;
    }
    
    public static int getAllowance(String departmentID) throws SQLException{
        conn = objDBUtil.connect();
        cs = conn.prepareCall("{call spGetAllowanceDepartment(?)}");
        cs.setString(1, departmentID);
        rs = cs.executeQuery();
        int allowance = 0;
        while (rs.next()) {            
            allowance = rs.getInt(1);
        }
        return allowance;
    }
}
